package lk.icta.core.dao.impl;

import lk.icta.common.AppConstants;
import lk.icta.common.dao.impl.BaseDAO;
import lk.icta.core.dao.LandDAO;
import lk.icta.core.entity.Land;
import lk.icta.user.entity.User;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;

import javax.persistence.Query;
import java.util.Date;
import java.util.List;

public class LandDAOImpl extends BaseDAO implements LandDAO {

    private static final Logger logger = LoggerFactory.getLogger(LandDAOImpl.class);

    @Transactional(propagation = Propagation.MANDATORY)
    public void addLand(Land land, User user) {
        logger.debug("Adding a Land {} by {}", land.getNameOfTheLand(), user.getUsername());
        land.getLifeCycleInfo().setCreatedUser(user);
        land.getLifeCycleInfo().setCreatedTimeStamp(new Date());
        land.getLifeCycleInfo().setLastUpdatedUser(user);
        land.getLifeCycleInfo().setLastUpdatedTimeStamp(new Date());
        em.persist(land);
    }

    @Transactional(propagation = Propagation.MANDATORY)
    public void updateLand(Land land, User user) {
        logger.debug("Updating a Land {} by {}", land.getId(), user.getUsername());
        logger.debug("Updating a Land ------------------------------- version - {}", land.getVersion());
        land.getLifeCycleInfo().setLastUpdatedUser(user);
        land.getLifeCycleInfo().setLastUpdatedTimeStamp(new Date());
        em.merge(land);
    }

    @Transactional(propagation = Propagation.MANDATORY)
    public void deleteLand(Land land, User user) {
        logger.debug("Deleting land {} by {}", land.getId(), user.getFullName());
        land.getLifeCycleInfo().setLastUpdatedUser(user);
        land.getLifeCycleInfo().setLastUpdatedTimeStamp(new Date());
        land.setAlienationState(Land.AlienationState.ARCHIVED);
        land.setActive(false);
        em.merge(land);
    }

    @Transactional(propagation = Propagation.MANDATORY)
    public void deleteLandFromDatabase(long landId, User user) {
        logger.debug("Deleting land {} by {}", landId, user.getFullName());
        Query q = em.createNamedQuery("deleteLandById");
        q.setParameter("landId", landId);
        q.executeUpdate();

    }

    @Transactional(propagation = Propagation.SUPPORTS, readOnly = true)
    public Land getLandByIdUKey(long idUKey, User user) {
        logger.debug("Loading Land [Land Id: {}]", idUKey);
        return em.find(Land.class, idUKey);
    }

    @Transactional(propagation = Propagation.NEVER, readOnly = true)
    public List<Land> getLandByName(String nameOfTheLand, User user) {
        logger.debug("Loading land list by name [Name: {}]", nameOfTheLand);
        Query q = em.createNamedQuery("getLandByName");
        q.setParameter("nameOfTheLand", nameOfTheLand);
        return q.getResultList();
    }

    @Transactional(propagation = Propagation.NEVER, readOnly = true)
    public List<Land> getLandsByGNDivision(int gnDivisionId, User user) {
        logger.debug("Loading land list by GNDivision [GNDivision Id: {}]", gnDivisionId);
        Query q = em.createNamedQuery("getLandsByGNDivision");
        q.setParameter("gnDivisionId", gnDivisionId);
        return q.getResultList();
    }

    @Transactional(propagation = Propagation.NEVER, readOnly = true)
    public List<Land> getActiveLandsByGNDivision(int gnDivisionId, User user) {
        logger.debug("Loading Active land list by GNDivision [GNDivision id: {}]", gnDivisionId);
        Query q = em.createNamedQuery("getActiveLandsByGNDivision");
        q.setParameter("gnDivisionId", gnDivisionId);
        return q.getResultList();
    }

    @Transactional(propagation = Propagation.NEVER, readOnly = true)
    public List<Land> getInitialActiveLandsByGNDivision(int gnDivisionId, User user) {
        logger.debug("Listing Initial Lands in GN Division {} by {}", gnDivisionId, user.getUsername());
        Query q = em.createNamedQuery("getInitialActiveLandsByGNDivision");
        q.setParameter("gnDivisionId", gnDivisionId);
        return q.getResultList();
    }

    @Transactional(propagation = Propagation.NEVER, readOnly = true)
    public List<Land> getInitialActiveLandsByDSDivision(int dsDivisionId, User user) {
        logger.debug("Listing Initial Lands in D.S. Division {} by {}", dsDivisionId, user.getUsername());
        Query q = em.createNamedQuery("getInitialActiveLandsByDSDivision");
        q.setParameter("dsDivisionId", dsDivisionId);
        return q.getResultList();
    }

    @Transactional(propagation = Propagation.NEVER, readOnly = true)
    public List<Land> getUnalienatedLandList(User user) {
        logger.debug("Get Unalienated Lands By User - {}", user.getUsername());
        Query q = em.createNamedQuery("getUnalienatedLandList");
        return q.getResultList();
    }

    @Transactional(propagation = Propagation.NEVER, readOnly = true)
    public List<Land> getDetailActiveLandsByGNDivision(int gnDivisionId, User user) {
        logger.debug("Listing Detail Lands in GN Division {} by {}", gnDivisionId, user.getUsername());
        Query q = em.createNamedQuery("getDetailActiveLandsByGNDivision");
        q.setParameter("gnDivisionId", gnDivisionId);
        return q.getResultList();
    }

    @Transactional(propagation = Propagation.NEVER, readOnly = true)
    public List<Land> getIncompleteDetailLandsByGNDivision(int gnDivisionId, User user) {
        logger.debug("Listing Incomplete Detail Lands in GN Division {} by {}", gnDivisionId, user.getUsername());
        Query q = em.createNamedQuery("getIncompleteDetailLandsByGNDivision");
        q.setParameter("gnDivisionId", gnDivisionId);
        return q.getResultList();
    }

    @Transactional(propagation = Propagation.NEVER, readOnly = true)
    public List<Land> getAllIncompleteDetailLands(User user) {
        logger.debug("Listing all incomplete detailed land records by {}", user.getUsername());
        Query q = em.createNamedQuery("getAllIncompleteDetailLands");
        q.setParameter("dsDivisionId", user.getDsDivisions().iterator().next().getId());
        return q.getResultList();
    }

    @Transactional(propagation = Propagation.NEVER, readOnly = true)
    public List<Land> getDetailLandReportByGNDivision(int gnDivisionId, User user) {
        logger.debug("Listing Report Detail Lands in GN Division {} by {}", gnDivisionId, user.getUsername());
        Query q = em.createNamedQuery("getDetailLandsReportByGNDivision");
        q.setParameter("gnDivisionId", gnDivisionId);
        return q.getResultList();
    }

    @Transactional(propagation = Propagation.NEVER, readOnly = true)
    public List<Land> getActivePendingLandsByGNDivision(int gnDivisionId, User user) {
        logger.debug("Loading Active, Pending land list by GNDivision [GNDivision id: {}]", gnDivisionId);
        Query q = em.createNamedQuery("getActivePendingLandsByGNDivision");
        q.setParameter("gnDivisionId", gnDivisionId);
        return q.getResultList();
    }

    @Transactional(propagation = Propagation.NEVER, readOnly = true)
    public List<Land> getRejectedLandsByGNDivision(int gnDivisionId, User user) {
        logger.debug("Loading reject land list by GNDivision [GNDivision id: {}]", gnDivisionId);
        Query q = em.createNamedQuery("getRejectedLandsByGNDivision");
        q.setParameter("gnDivisionId", gnDivisionId);
        return q.getResultList();
    }

    @Transactional(propagation = Propagation.NEVER, readOnly = true)
    public List<Land> getSubDividedLandsTobeApproveByGNDivision(int gnDivisionId, User user) {
        logger.debug("Loading Sub divide land list by GNDivision [GNDivision id: {}]", gnDivisionId);
        Query q = em.createNamedQuery("getSubDividedLandsTobeApproveByGNDivision");
        q.setParameter("gnDivisionId", gnDivisionId);
        return q.getResultList();
    }

    @Transactional(propagation = Propagation.NEVER, readOnly = true)
    public List<Land> getSubDividedLandsTobeEditByGNDivision(int gnDivisionId, User user) {
        logger.debug("Loading Sub divide land edit list by GNDivision [GNDivision id: {}]", gnDivisionId);
        Query q = em.createNamedQuery("getSubDividedLandsTobeEditByGNDivision");
        q.setParameter("gnDivisionId", gnDivisionId);
        return q.getResultList();
    }

    @Transactional(propagation = Propagation.NEVER, readOnly = true)
    public List<Land> getActivePendingLandsByDSDivision(User user) {
        Query q = em.createNamedQuery("getActivePendingLandsByDSDivision");
        q.setParameter("dsDivisionId", user.getDsDivisions().iterator().next().getId());
        return q.getResultList();
    }

    @Transactional(propagation = Propagation.NEVER, readOnly = true)
    public List<Land> getActivePendingLandsByDSDivisionId(int dsDivisionId, User user) {
        Query q = em.createNamedQuery("getActivePendingLandsByDSDivision");
        q.setParameter("dsDivisionId", dsDivisionId);
        return q.getResultList();
    }

    @Transactional(propagation = Propagation.NEVER, readOnly = true)
    public List<Land> getRejectedLandsByDSDivision(User user) {
        Query q = em.createNamedQuery("getRejectedLandsByDSDivision");
        q.setParameter("dsDivisionId", user.getDsDivisions().iterator().next().getId());
        return q.getResultList();
    }

    @Transactional(propagation = Propagation.NEVER, readOnly = true)
    public List<Land> getSubDividedLandsTobeApproveByDSDivision(User user) {
        Query q = em.createNamedQuery("getSubDividedLandsTobeApproveByDSDivision");
        q.setParameter("dsDivisionId", user.getDsDivisions().iterator().next().getId());
        return q.getResultList();
    }

    @Transactional(propagation = Propagation.NEVER, readOnly = true)
    public List<Land> getSubDividedLandsTobeEditByDSDivision(User user) {
        Query q = em.createNamedQuery("getSubDividedLandsTobeEditByDSDivision");
        q.setParameter("dsDivisionId", user.getDsDivisions().iterator().next().getId());
        return q.getResultList();
    }

    @Transactional(propagation = Propagation.NEVER, readOnly = true)
    public List<Land> getAllActivePendingLands(User user) {
        logger.debug("Loading all Active, Pending lands");
        Query q = em.createNamedQuery("getAllActivePendingLands");
        q.setParameter("dsDivisionId", user.getDsDivisions().iterator().next().getId());
        return q.getResultList();
    }

    @Transactional(propagation = Propagation.NEVER, readOnly = true)
    public List<Land> getActiveLandsByGNDivisionListAndState(User user, Land.ApprovalState state, Land.Mode mode) {
        if (user.getDsDivisions() == null || user.getDsDivisions().isEmpty()) {
            return null;
        } else {
            logger.debug("Get Active Lands By GNDivision List And State [GNDivision List size : {}]",
                    user.getDsDivisions().size());
            Query q = em.createNamedQuery("getLandsByDivisionListAndStateAndMode");
            q.setParameter("dsDivisionList", user.getDsDivisions());
            q.setParameter("mode", mode);
            q.setParameter("state", state);
            return q.getResultList();
        }
    }

    @Transactional(propagation = Propagation.NEVER, readOnly = true)
    public List<Land> getLandsByDSDivision(int dsDivisionId, User user) {
        logger.debug("Loading land list by DSDivision [DSDivision Id: {}]", dsDivisionId);
        Query q = em.createNamedQuery("getLandsByDSDivision");
        q.setParameter("dsDivisionId", dsDivisionId);
        return q.getResultList();
    }

    @Transactional(propagation = Propagation.NEVER, readOnly = true)
    public List<Land> getActiveLandsByDSDivision(int dsDivisionId, User user) {
        logger.debug("Loading Active land list by DSDivision [DSDivision id: {}]", dsDivisionId);
        Query q = em.createNamedQuery("getActiveLandsByDSDivision");
        q.setParameter("dsDivisionId", dsDivisionId);
        return q.getResultList();
    }

    @Transactional(propagation = Propagation.NEVER, readOnly = true)
    public List<Land> getLandsByDistrict(int districtId, User user) {
        logger.debug("Loading land list by District [District Id: {}]", districtId);
        Query q = em.createNamedQuery("getLandsByDistrict");
        q.setParameter("districtId", districtId);
        return q.getResultList();
    }

    @Transactional(propagation = Propagation.NEVER, readOnly = true)
        public List<Land> getLandsByDistrictRail(int districtId, User user) {
            logger.debug("Loading land list by District Rail [District Id: {}]", districtId);
            Query q = em.createNamedQuery("getLandsByDistrictRail");
            q.setParameter("districtId", user.getDistricts().iterator().next().getId());
            return q.getResultList();
        }

    @Transactional(propagation = Propagation.NEVER, readOnly = true)
    public List<Land> getActiveLandsByDistrict(int districtId, User user) {
        logger.debug("Loading Active land list by District [District id: {}]", districtId);
        Query q = em.createNamedQuery("getActiveLandsByDistrict");
        q.setParameter("districtId", districtId);
        return q.getResultList();
    }

    @Transactional(propagation = Propagation.NEVER, readOnly = true)
    public List<Land> getLandsByProvince(int provinceId, User user) {
        logger.debug("Loading land list by Province [Province Id: {}]", provinceId);
        Query q = em.createNamedQuery("getLandsByProvince");
        q.setParameter("provinceId", provinceId);
        return q.getResultList();
    }

    @Transactional(propagation = Propagation.NEVER, readOnly = true)
    public Land getLandByPlanNo(String planNo, User user) {
        logger.debug("Loading land list by PlanNo [PlanNo: {}]", planNo);
        Query q = em.createNamedQuery("getLandByPlanNo");
        q.setParameter("planNo", planNo);
        return (Land) q.getSingleResult();
    }

    @Transactional(propagation = Propagation.SUPPORTS, readOnly = true)
    public Land getLandById(long id) {
        logger.debug("Loading Land [Land Id: {}]", id);
        return em.find(Land.class, id);
    }

    @Transactional(propagation = Propagation.NEVER, readOnly = true)
    public Land getLandByRef(long referenceNumber) {
        logger.debug("land id  {}", referenceNumber);
        Query q = em.createNamedQuery("getLandByRef");
        q.setParameter("referenceNumber", referenceNumber);
        logger.debug("list size is  {}", q.getResultList().size());
        if (q.getResultList().size() > 0) {
            return (Land) q.getResultList().get(0);
        } else {
            return null;
        }
    }

    @Transactional(propagation = Propagation.NEVER, readOnly = true)
    public List<Land> getApprovedUnAlienatedLandsByGNDivision(int gnDivisionId, User user) {
        logger.debug("Loading approved, UnAlienated lands of GN Division {} by {}", gnDivisionId, user.getUsername());
        Query q = em.createNamedQuery("getApprovedUnAlienatedLandsByGNDivision");
        q.setParameter("gnDivisionId", gnDivisionId);
        return q.getResultList();
    }

    @Transactional(propagation = Propagation.NEVER, readOnly = true)
    public Land getApprovedUnAlienatedLandById(long id) {
        logger.debug("Loading approved, UnAlienated lands  by id{}", id);
        Query q = em.createNamedQuery("getApprovedUnAlienatedLandById");
        q.setParameter("id", id);
        if (q.getResultList().size() > 0) {
            return (Land) q.getResultList().get(0);
        } else {
            return null;
        }
    }

    @Transactional(propagation = Propagation.NEVER, readOnly = true)
    public List<Land> getAllApprovedUnAlienatedLands(User user) {
        logger.debug("Loading all approved, UnAlienated lands");
        Query q = em.createNamedQuery("getAllApprovedUnAlienatedLands");
        q.setParameter("dsDivisionId", user.getDsDivisions().iterator().next().getId());
        return q.getResultList();
    }

    @Transactional(propagation = Propagation.NEVER, readOnly = true)
    public List<Land> getApprovedLandsByGNDivision(int gnDivisionId, User user) {
        logger.debug("Loading approved lands of GN Division {} by {}", gnDivisionId, user.getUsername());
        Query q = em.createNamedQuery("getApprovedLandsByGNDivision");
        q.setParameter("gnDivisionId", gnDivisionId);
        return q.getResultList();
    }

    @Transactional(propagation = Propagation.NEVER, readOnly = true)
    public List<Land> getAllApprovedLandsByUser(User user) {
        logger.debug("Loading All approved lands  by {}", user.getUsername());
        Query q = em.createNamedQuery("getAllApprovedLandsByUser");
        q.setParameter("dsDivisionId", user.getDsDivisions().iterator().next().getId());
        return q.getResultList();
    }

    @Transactional(propagation = Propagation.NEVER, readOnly = true)
    public Boolean isExistAlienation(long landId) {
        logger.debug("Check whether the land {} has an active alienation", landId);
        Query q = em.createNamedQuery("isExistAlienation");
        q.setParameter("landId", landId);
        if (q.getResultList().isEmpty()) {
            return false;
        } else {
            return true;
        }
    }

    @Transactional(propagation = Propagation.NEVER, readOnly = true)
    public List<Land> searchActiveLandsByLandName(String landName, User user) {
        logger.debug("Search active lands by land name: {} by {}", landName, user.getUsername());
        Query q = em.createNamedQuery("searchActiveLandsByLandName");
        q.setParameter("landName", "%" + landName + "%");
        return q.getResultList();
    }

    @Transactional(propagation = Propagation.NEVER, readOnly = true)
    public List<Land> searchActiveLandsByPlanNo(String planNo, User user) {
        logger.debug("Search active lands by plan no: {} by {}", planNo, user.getUsername());
        Query q = em.createNamedQuery("searchActiveLandsByPlanNo");
        q.setParameter("planNo", "%" + planNo + "%");
        return q.getResultList();
    }

    @Transactional(propagation = Propagation.NEVER, readOnly = true)
    public List<Land> searchActiveLandsByLotNo(String lotNo, User user) {
        logger.debug("Search active lands by lot no: {} by {}", lotNo, user.getUsername());
        Query q = em.createNamedQuery("searchActiveLandsByLotNo");
        q.setParameter("lotNo", "%" + lotNo + "%");
        return q.getResultList();
    }

    @Transactional(propagation = Propagation.NEVER, readOnly = true)
    public List<Land> searchActiveLandsByHolderName(String holderName, User user) {
        logger.debug("Search active land by holder name: {} by {}", holderName, user.getUsername());
        Query q = em.createNamedQuery("searchActiveLandsByHolderName");
        q.setParameter("holderName", "%" + holderName + "%");
        return q.getResultList();
    }

    @Transactional(propagation = Propagation.NEVER, readOnly = true)
    public List<Land> searchAllActiveApprovedLands() {
        logger.debug("Search all active approved lands");
        Query q = em.createNamedQuery("searchAllActiveApprovedLands");
        return q.getResultList();
    }

    @Transactional(propagation = Propagation.NEVER, readOnly = true)
    public List<Land> searchActiveLandsOfDSDivisionByLandName(String landName, int dsId, User user) {
        logger.debug("Search active lands by land name: {} of {}", landName, user.getUsername());
        Query q = em.createNamedQuery("searchActiveLandsOfDSDivisionIDByLandName");
        q.setParameter("landName", "%" + landName + "%");
        q.setParameter("dsDivisionId", dsId);
        return q.getResultList();
    }

    @Transactional(propagation = Propagation.NEVER, readOnly = true)
    public List<Land> searchActiveLandsOfDSDivisionByLandName(String landName, User user) {
        logger.debug("Search active lands by land name: {} of {}", landName, user.getUsername());
        Query q = em.createNamedQuery("searchActiveLandsOfDSDivisionByLandName");
        q.setParameter("landName", "%" + landName + "%");
        populateDsDivisionList(q, user);

        return q.getResultList();
    }

    @Transactional(propagation = Propagation.NEVER, readOnly = true)
    public List<Land> searchActiveLandsOfDSDivisionByPlanNo(String planNo, User user) {
        logger.debug("Search active lands by plan no: {} of {}", planNo, user.getUsername());
        Query q = em.createNamedQuery("searchActiveLandsOfDSDivisionByPlanNo");
        q.setParameter("planNo", "%" + planNo + "%");
        populateDsDivisionList(q, user);

        return q.getResultList();
    }

    @Transactional(propagation = Propagation.NEVER, readOnly = true)
    public List<Land> searchActiveLandsOfDSDivisionByLotNo(String lotNo, User user) {
        logger.debug("Search active lands by lot no: {} of {}", lotNo, user.getUsername());
        Query q = em.createNamedQuery("searchActiveLandsOfDSDivisionByLotNo");
        q.setParameter("lotNo", "%" + lotNo + "%");
        populateDsDivisionList(q, user);

        return q.getResultList();
    }

    @Transactional(propagation = Propagation.NEVER, readOnly = true)
    public List<Land> searchActiveLandsOfDSDivisionByHolderName(String holderName, User user) {
        logger.debug("Search active land by holder name: {} of {}", holderName, user.getUsername());
        Query q = em.createNamedQuery("searchActiveLandsOfDSDivisionByHolderName");
        q.setParameter("holderName", "%" + holderName + "%");
        populateDsDivisionList(q, user);

        return q.getResultList();
    }

    @Transactional(propagation = Propagation.NEVER, readOnly = true)
    public List<Land> searchAllActiveApprovedLandsOfDSDivision(int dsDivisionId) {
        logger.debug("Search all active approved lands of {}", dsDivisionId);
        Query q = em.createNamedQuery("searchAllActiveApprovedLandsOfDSDivision");
        q.setParameter("dsDivisionId", dsDivisionId);
        return q.getResultList();
    }

    @Transactional(propagation = Propagation.NEVER, readOnly = true)
    public List<Land> searchAllActiveApprovedUnalienatedLandsOfDSDivision(int dsDivisionId) {
        logger.debug("Search all active approved unalienated lands of {}", dsDivisionId);
        Query q = em.createNamedQuery("searchAllActiveApprovedUnalienatedLandsOfDSDivision");
        q.setParameter("dsDivisionId", dsDivisionId);
        return q.getResultList();
    }

    @Transactional(propagation = Propagation.NEVER, readOnly = true)
    public List<Land> searchAllActiveApprovedLandsOfDistrict(int districtId) {
        logger.debug("Search all active approved lands of {}", districtId);
        Query q = em.createNamedQuery("searchAllActiveApprovedLandsOfDistrict");
        q.setParameter("districtId", districtId);
        return q.getResultList();
    }

    @Transactional(propagation = Propagation.NEVER, readOnly = true)
    public List<Land> searchChildrenByParentLand(User user, long landId) {
        logger.debug("search Children By Parent Land {}", landId);
        Query q = em.createNamedQuery("searchChildrenByParentLand");
        q.setParameter("landId", landId);
        return q.getResultList();
    }

    @Transactional(propagation = Propagation.NEVER, readOnly = true)
    public List<Land> searchAllChildrenByParentLand(User user, long landId) {
        logger.debug("search All Children By Parent Land {}", landId);
        Query q = em.createNamedQuery("searchAllChildrenByParentLand");
        q.setParameter("landId", landId);
        return q.getResultList();
    }

    @Transactional(propagation = Propagation.NEVER, readOnly = true)
    public List<Land> searchChildrenByParentReferenceNumber(User user, long landReferenceNumber) {
        logger.debug("search Children By Parent Reference Number {}", landReferenceNumber);
        Query q = em.createNamedQuery("searchChildrenByParentReferenceNumber");
        q.setParameter("landReferenceNumber", landReferenceNumber);
        return q.getResultList();
    }

    @Transactional(propagation = Propagation.NEVER, readOnly = true)
    public List<Land> searchLandByPlanNo(User user, String planNo) {
        logger.debug("searchLandByPlanNo {}", planNo);
        Query q = em.createNamedQuery("searchLandByPlanNo");
        q.setParameter("planNumber", planNo);
        return q.getResultList();
    }

    @Transactional(propagation = Propagation.NEVER, readOnly = true)
    public List<Land> searchLandByPlanNoAndLotNo(User user, String planNo, String lotNo) {
        logger.debug("searchLandByPlanNoAndLotNo {}", planNo, lotNo);
        Query q = em.createNamedQuery("searchLandByPlanNoAndLotNo");
        q.setParameter("planNumber", planNo);
        q.setParameter("lotNumber", lotNo);
        return q.getResultList();
    }

    @Transactional(propagation = Propagation.NEVER, readOnly = true)
    public List<Land> getAllActivePendingIncompleteLands(User user, int pageNo, int noOfRows) {
        logger.debug("Loading all Active, Pending lands");
        Query q = em.createNamedQuery("getAllActivePendingIncompleteLands").setFirstResult(
                (pageNo - 1) * noOfRows).setMaxResults(noOfRows);
        q.setParameter("dsDivisionId", user.getDsDivisions().iterator().next().getId());
        return q.getResultList();
    }

    @Transactional(propagation = Propagation.NEVER, readOnly = true)
    public List<Land> getAllActivePendingIncompleteLandsByGNDivision(User user, int gnDivisionId, int pageNo, int noOfRows) {
        logger.debug("Loading all Active, Pending lands");
        Query q = em.createNamedQuery("getAllActivePendingIncompleteLandsByGNDivision").setFirstResult(
                (pageNo - 1) * noOfRows).setMaxResults(noOfRows);
        q.setParameter("gnDivisionId", gnDivisionId);
        return q.getResultList();
    }

    @Transactional(propagation = Propagation.NEVER, readOnly = true)
    public List<Land> searchAllLandsReport(int divisionId, String searchBy, Land.AlienationState alienationState,
                                           int alienationMethodId, int geoTypeId, int pageNo) {

        String query = "SELECT l FROM Land l";
        if (alienationState == Land.AlienationState.ALIENATED && alienationMethodId != 0) query += ", Alienation a";
        if (alienationState != null || searchBy != null || geoTypeId != 0) {
            boolean isAdded = false;
            query += " WHERE";
            if (alienationState != null) {
                if (alienationState == Land.AlienationState.ALIENATED && alienationMethodId != 0) {
                    query += " l.id = a.land.id AND l.alienationState = :alienationState AND a.alienationMethod.id = :alienationMethod";
                } else {
                    query += " l.alienationState = :alienationState";
                }
                isAdded = true;
            }

            if (geoTypeId != 0) {
                query += isAdded ? " AND " : "";
                query += "l.geologicalType.id = :geoType";
                isAdded = true;
            }

            if (searchBy != null) {
                query += isAdded ? " AND " : "";
                query += searchBy;
            }
        }

        Query q = em.createQuery(query);

        if (searchBy != null) q.setParameter("divisionId", divisionId);
        if (alienationState != null) q.setParameter("alienationState", alienationState);
        if (alienationMethodId != 0) q.setParameter("alienationMethod", alienationMethodId);
        if (geoTypeId != 0) q.setParameter("geoType", geoTypeId);
        logger.debug("Dynamic Query  : {}", query);
        q.setFirstResult((pageNo - 1) * AppConstants.MAX_ROWS_PER_PAGE).setMaxResults(AppConstants.MAX_ROWS_PER_PAGE);
        return q.getResultList();

    }

    @Transactional(propagation = Propagation.NEVER, readOnly = true)
    public List<Object[]> getAllLandStatistics(int id, String searchBy, Land.ApprovalState landState, Date fromDate, Date toDate, int migState) {
        String query = "SELECT l.approvalState AS landState, COUNT(l) AS count FROM Land l";
        if (landState != null || searchBy != null || (fromDate != null && toDate != null) || migState > 0) {
            boolean isAdded = false;
            query += " WHERE ";
            if (searchBy != null) {
                query += searchBy;
                isAdded = true;
            }

            if ((fromDate != null && toDate != null)) {
                query += isAdded ? " AND " : "";
                query += "l.lifeCycleInfo.lastUpdatedTimeStamp BETWEEN :start AND :end";
            }

            if (landState != null) {
                query += isAdded ? " AND " : "";
                query += "l.approvalState = :landState";
            }

            if (migState > 0) {
                query += isAdded ? " AND " : "";
                if (migState == 1) {
                    query += "l.lifeCycleInfo.createdUser.username = 'admin'";
                } else {
                    query += "l.lifeCycleInfo.createdUser.username <> 'admin'";
                }
            }

        }
        query += " GROUP BY l.approvalState";

        logger.debug("Dynamic Statistics Query  : {}", query);
        Query q = em.createQuery(query);

        if (searchBy != null) q.setParameter("divisionId", id);
        if ((fromDate != null && toDate != null)) {
            q.setParameter("start", fromDate);
            q.setParameter("end", toDate);
        }
        if (landState != null) q.setParameter("landState", landState);

        List<Object[]> result = q.getResultList();
        return result;
    }

    @Transactional(propagation = Propagation.NEVER, readOnly = true)
    public List<Land> searchAllLandsOfDSDivision(String landName,String planNo,String lotNo,String holderName,int dsDivisionId, int pageNo) {
        logger.debug(" Upper level Search All lands of dsDivision {}", dsDivisionId);
        int noOfRows = AppConstants.MAX_ROWS_PER_PAGE;
        String holderQuery  = " AND ( EXISTS (SELECT a FROM Alienation a " +
                "WHERE l.id = a.land.id AND a.holder.name LIKE :holderName) )" ;
        if( holderName == null || holderName.trim().equals("")){
            holderQuery="";
        }

        String query = "SELECT l FROM Land l WHERE " +
                "l.alienationState <> 2  AND l.nameOfTheLand LIKE :landName AND " +
                "l.planNumber LIKE :planNo AND "+
                "l.lotNumber LIKE :lotNo  "+
                holderQuery +
                " AND l.gnDivision.dsDivision.id = :dsDivisionId " +
                "ORDER BY l.id";

        Query q =em.createQuery(query).setFirstResult((pageNo - 1) * noOfRows).setMaxResults(noOfRows);
        q.setParameter("landName", "%" + landName + "%");
        q.setParameter("planNo", "%" + planNo + "%");
        q.setParameter("lotNo", "%" + lotNo + "%");
        if(!holderQuery.equals("")){
            q.setParameter("holderName", "%" + holderName + "%");
        }
        q.setParameter("dsDivisionId", dsDivisionId);
        return q.getResultList();
    }

    @Transactional(propagation = Propagation.NEVER, readOnly = true)
    public List<Land> searchAllLandsOfDistrict(String landName,String planNo,String lotNo,String holderName,int districtId, int pageNo) {
        logger.debug("Search All  lands of district {}", districtId);
        int noOfRows = AppConstants.MAX_ROWS_PER_PAGE;
        //holder query needs checking aliention table avoid if text holder search text is empty
        String holderQuery = " AND ( EXISTS (SELECT a FROM Alienation a " +
                "WHERE l.id = a.land.id AND a.holder.name LIKE :holderName) )";

        if (holderName == null || holderName.trim().equals("")) {
            holderQuery = "";
        }

        String query = "SELECT l FROM Land l WHERE " +
                "l.alienationState <> 2  AND l.nameOfTheLand LIKE :landName AND " +
                "l.planNumber LIKE :planNo AND " +
                "l.lotNumber LIKE :lotNo  " +
                holderQuery +
                " AND l.gnDivision.dsDivision.district.id = :districtId " /*+
                "ORDER BY l.id"*/;

        Query q = em.createQuery(query).setFirstResult((pageNo - 1) * noOfRows).setMaxResults(noOfRows);
        q.setParameter("landName", "%" + landName + "%");
        q.setParameter("planNo", "%" + planNo + "%");
        q.setParameter("lotNo", "%" + lotNo + "%");
        if (!holderQuery.equals("")) {
            q.setParameter("holderName", "%" + holderName + "%");
        }
        q.setParameter("districtId", districtId);
        return q.getResultList();
    }

    @Transactional(propagation = Propagation.NEVER, readOnly = true)
    public List<Land>   searchAllLandsOfDSDivisionBySearchText(String landName,String planNo,String lotNo,String holderName, User user, int pageNo) {
        logger.debug("Search All  lands  by name {} user {} ", landName, user.getUsername());
        int noOfRows = AppConstants.MAX_ROWS_PER_PAGE;
        //holder query needs checking aliention table avoid if text holder search text is empty
        String holderQuery = "( EXISTS (SELECT a FROM Alienation a " +
                "WHERE l.id = a.land.id AND a.holder.name LIKE :holderName) )";
        String holderQuery1 = holderQuery + " AND ";
        holderQuery = " AND " + holderQuery;
        if (holderName == null || holderName.trim().equals("")) {
            holderQuery = "";
            holderQuery1 = "";
        }

        String query = "SELECT l FROM Land l WHERE " +
                "(1 = :ignoreDS AND l.alienationState <> 2  AND l.nameOfTheLand LIKE :landName AND " +
                "l.planNumber LIKE :planNo AND " +
                "l.lotNumber LIKE :lotNo  " +
                holderQuery +
                ") OR " +
                "(0 = :ignoreDS AND l.alienationState <> 2  AND l.nameOfTheLand LIKE :landName AND " +
                "l.planNumber LIKE :planNo AND " +
                "l.lotNumber LIKE :lotNo AND " +
                holderQuery1 +
                "l.gnDivision.dsDivision IN (:DSList))";

        Query q = em.createQuery(query).setFirstResult((pageNo - 1) * noOfRows).setMaxResults(noOfRows);
        q.setParameter("landName", "%" + landName + "%");
        q.setParameter("planNo", "%" + planNo + "%");
        q.setParameter("lotNo", "%" + lotNo + "%");
        if (!holderQuery.equals("")) {
            q.setParameter("holderName", "%" + holderName + "%");
        }
        populateDsDivisionList(q, user);
        return q.getResultList();
    }

    @Transactional(propagation = Propagation.NEVER, readOnly = true)
    public List<Land> searchAllLandsOfManyDSDivisionsOfUpperLevel(String landName,String planNo,String lotNo,String holderName, User user, int pageNo) {
        logger.debug("Search all lands of many divisions username: {} pageNo {}", user.getUsername(), pageNo);
        int noOfRows = AppConstants.MAX_ROWS_PER_PAGE;
        String holderQuery = "( EXISTS (SELECT a FROM Alienation a " +
                "WHERE l.id = a.land.id AND a.holder.name LIKE :holderName) )";
        String holderQuery1 = holderQuery + " AND ";
        holderQuery = " AND " + holderQuery;

        if (holderName == null || holderName.trim().equals("")) {
            holderQuery = "";
            holderQuery1 = "";
        }

        String query = "SELECT l FROM Land l WHERE " +
                "(1 = :ignoreDS AND l.alienationState <> 2  AND l.nameOfTheLand LIKE :landName AND " +
                "l.planNumber LIKE :planNo AND " +
                "l.lotNumber LIKE :lotNo  " +
                holderQuery +
                ") OR " +
                "(0 = :ignoreDS AND l.alienationState <> 2  AND l.nameOfTheLand LIKE :landName AND " +
                "l.planNumber LIKE :planNo AND " +
                "l.lotNumber LIKE :lotNo AND " +
                holderQuery1 +
                "l.gnDivision.dsDivision IN (:DSList))";

        Query q = em.createQuery(query).setFirstResult((pageNo - 1) * noOfRows).setMaxResults(noOfRows);
        q.setParameter("landName", "%" + landName + "%");
        q.setParameter("planNo", "%" + planNo + "%");
        q.setParameter("lotNo", "%" + lotNo + "%");
        if (!holderQuery.equals("")) {
            q.setParameter("holderName", "%" + holderName + "%");
        }
        populateDsDivisionList(q, user);
        return q.getResultList();
    }

    @Transactional(propagation = Propagation.NEVER, readOnly = true)
    public List<Land> getAllActivePendingIncompleteLandsByDsDivision(User user,int dsDivision, int pageNo, int noOfRows) {
        logger.debug("Loading all Active, Pending, incomplete lands by DsDivision {}",dsDivision);
        Query q = em.createNamedQuery("getAllActivePendingIncompleteLands").setFirstResult(
                (pageNo - 1) * noOfRows).setMaxResults(noOfRows);
        q.setParameter("dsDivisionId", dsDivision);
        return q.getResultList();
    }

    private void populateDsDivisionList(Query q, User user) {

        if (user.getDsDivisions() != null && user.getDsDivisions().size() != 0) {
            //for orlp and lcg, find all apps, ignore DS filter
            logger.debug("ds div list size : {}", user.getDsDivisions().size());
            q.setParameter("ignoreDS", 0);
            q.setParameter("DSList", user.getDsDivisions());

        } else {
            q.setParameter("ignoreDS", 1);
            logger.debug("ds div list : {} ignoreDS :{}", user.getDsDivisions(), 1);
            q.setParameter("DSList", null);
        }

    }

}
